package uczelniabd2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

// =============================================================================
public class DatabaseMgr
{
	public DatabaseMgr()
	{
		mConnection = null;
		org.jdesktop.application.ResourceMap resourceMap =
				org.jdesktop.application.Application.getInstance(
				uczelniabd2.UczelniaBD2App.class).getContext()
				.getResourceMap(DatabaseMgr.class);
		mDriverName = resourceMap.getString("database.driver");
		mConInfo = resourceMap.getString("database.url");
		mLogin = resourceMap.getString("database.user");
		mPass = resourceMap.getString("database.pass");
	}
// =============================================================================
	public boolean Connect()
	{
		try
		{
			Class.forName(mDriverName);
			mConnection = DriverManager.getConnection(mConInfo, mLogin, mPass);
			return true;
		}
		catch (Exception e)
		{
			mConnection = null;
			e.printStackTrace();
		}
		return false;
	}
// =============================================================================
	public void Disconnect()
	{
		try
		{
			mConnection.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================
	public Connection GetConnection()
	{
		return mConnection;
	}
// =============================================================================
	public String[][] GetTableColumnNames(String tableName, int page)
	{
		String[][] ret;

		try
		{
			Connect();

			Statement stat = mConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			stat.execute("SELECT column_name FROM all_tab_columns WHERE table_name='" + tableName + "'");

			ResultSet resultSet = stat.getResultSet();
			resultSet.last();
			int numRows = resultSet.getRow();
			resultSet.beforeFirst();

			ret = new String[numRows][1];

			int i = 0;
			while (resultSet.next())
			{
				ret[i][0] = resultSet.getString(1);
				i++;
			}

			resultSet.close();
			stat.close();

			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}

		return ret;
	}
// =============================================================================
	public String[][] GetTableData(String tableName, int page)
	{
		String[][] ret;

		try
		{
			Connect();

			int firstLimit = 1 + (page*mRowsPerPage);
			int stopLimit = firstLimit + (page*mRowsPerPage);

			Statement stat = mConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			stat.execute("select * from "+tableName
					+" where rownum < "+stopLimit
					+" minus"
					+" select * from "+tableName
					+" where rownum < "+firstLimit);

			ResultSet resultSet = stat.getResultSet();
			resultSet.last();
			int numRows = resultSet.getRow();
			resultSet.beforeFirst();

			ResultSetMetaData rsMetaData = resultSet.getMetaData();
			int numCols = rsMetaData.getColumnCount();

			ret = new String[numRows + 1][numCols];

			// Pierwszy wiersz to nazwy kolumn
			for(int cols = 0; cols < numCols; ++cols)
			{
				ret[0][cols] = rsMetaData.getColumnLabel(cols);
			}

			// Reszta danych
			int row = 1;
			while (resultSet.next())
			{
				for(int cols = 0; cols < numCols; ++cols)
				{
					ret[row][cols] = resultSet.getString(cols+1);
				}
				row++;
			}

			resultSet.close();
			stat.close();

			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}

		return ret;
	}
// =============================================================================
	public String[][] GetTableRowWhere(String tableName, String idColumnName, String equalsTo)
	{
		String[][] ret;

		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			stat.executeQuery("select * from "+tableName
					+" where "+idColumnName
					+" = '"+equalsTo+"'");
			

			ResultSet resultSet = stat.getResultSet();
			resultSet.last();
			int numRows = resultSet.getRow();
			resultSet.beforeFirst();

			ResultSetMetaData rsMetaData = resultSet.getMetaData();
			int numCols = rsMetaData.getColumnCount();

			ret = new String[numRows][numCols];

			// Reszta danych
			int row = 0;
			while (resultSet.next())
			{
				for(int cols = 0; cols < numCols; ++cols)
				{
					ret[row][cols] = resultSet.getString(cols+1);
				}
				row++;
			}

			resultSet.close();
			stat.close();

			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}

		return ret;
	}
// =============================================================================
	public void addStudent(int numerIndeksu, String imie, String nazwisko, int numerSemestru,
					String dataUrodzenia, String adresDoKorespondencji, String adresEmail)
	{
		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement();
			stat.executeQuery("insert into MJANUSZE.\"STUDENT\" (\"NUMERINDEKSU\", \"IMIE\", \"NAZWISKO\", \"NUMERSEMESTRU\", \"ADRESEMAIL\", \"DATAURODZENIA\", \"ADRESDOKORESPONDENCJI\") values ('"
					+numerIndeksu+"','"+imie+"', '"+nazwisko+"', '"+numerSemestru+"', '"+adresEmail+"', TO_DATE('"+dataUrodzenia+"', 'RR/MM/DD'), '"+adresDoKorespondencji+"')");
			stat.close();
			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================
	public void deleteStudent(int numerIndeksu)
	{
		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement();
			stat.executeQuery("delete from MJANUSZE.\"STUDENT\" where MJANUSZE.\"STUDENT\".\"NUMERINDEKSU\" = "+ numerIndeksu + "");
			stat.close();
			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================
	public void updateStudent(int numerIndeksu, String imie, String nazwisko, int numerSemestru,
					String dataUrodzenia, String adresDoKorespondencji, String adresEmail)
	{
		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement();
			stat.executeQuery("update \"MJANUSZE\".\"STUDENT\" SET \"MJANUSZE\".\"STUDENT\".\"IMIE\"='"+imie
					+"', \"MJANUSZE\".\"STUDENT\".\"NAZWISKO\"='"+nazwisko
					+"', \"MJANUSZE\".\"STUDENT\".\"NUMERSEMESTRU\"='"+numerSemestru
					+"', \"MJANUSZE\".\"STUDENT\".\"ADRESEMAIL\"='"+adresEmail
					+"', \"MJANUSZE\".\"STUDENT\".\"DATAURODZENIA\"= TO_DATE('"+dataUrodzenia+"', 'RR/MM/DD')"
					+", \"MJANUSZE\".\"STUDENT\".\"ADRESDOKORESPONDENCJI\"='"+adresDoKorespondencji
					+"' WHERE \"MJANUSZE\".\"STUDENT\".\"NUMERINDEKSU\" = "+numerIndeksu+"");
			stat.close();
			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================
	public void addWykladowca(int idInstytut, String imie, String nazwisko, String tytulNaukowy,
					String dataUrodzenia, String adresDoKorespondencji, String adresEmail)
	{
		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement();
			stat.executeQuery("insert into MJANUSZE.\"WYKLADOWCA\" (\"IDINSTYTUT\", \"IMIE\", \"NAZWISKO\", \"TYTULNAUKOWY\", \"ADRESEMAIL\", \"DATAURODZENIA\", \"ADRESDOKORESPONDENCJI\") values ('"
					+idInstytut+"','"+imie+"', '"+nazwisko+"', '"+tytulNaukowy+"', '"+adresEmail+"', TO_DATE('"+dataUrodzenia+"', 'RR/MM/DD'), '"+adresDoKorespondencji+"')");
			stat.close();
			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================
	public void deleteWykladowca(int idWykladowca)
	{
		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement();
			stat.executeQuery("delete from MJANUSZE.\"WYKLADOWCA\" where MJANUSZE.\"WYKLADOWCA\".\"IDWYKLADOWCA\" = "+ idWykladowca + "");
			stat.close();
			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================
	public void updateWykladowca(int idWykladowca, int idInstytut, String imie, String nazwisko, String tytulNaukowy,
					String dataUrodzenia, String adresDoKorespondencji, String adresEmail)
	{
		try
		{
			Connect();
			Connection con = GetConnection();

			Statement stat = con.createStatement();
			stat.executeQuery("update \"MJANUSZE\".\"WYKLADOWCA\" SET \"MJANUSZE\".\"WYKLADOWCA\".\"IDINSTYTUT\"='"+idInstytut
					+"', \"MJANUSZE\".\"WYKLADOWCA\".\"IMIE\"='"+imie
					+"', \"MJANUSZE\".\"WYKLADOWCA\".\"NAZWISKO\"='"+nazwisko
					+"', \"MJANUSZE\".\"WYKLADOWCA\".\"TYTULNAUKOWY\"='"+tytulNaukowy
					+"', \"MJANUSZE\".\"WYKLADOWCA\".\"ADRESEMAIL\"='"+adresEmail
					+"', \"MJANUSZE\".\"WYKLADOWCA\".\"DATAURODZENIA\"= TO_DATE('"+dataUrodzenia+"', 'RR/MM/DD')"
					+", \"MJANUSZE\".\"WYKLADOWCA\".\"ADRESDOKORESPONDENCJI\"='"+adresDoKorespondencji
					+"' WHERE \"MJANUSZE\".\"WYKLADOWCA\".\"IDWYKLADOWCA\" = "+idWykladowca+"");
			stat.close();
			Disconnect();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
// =============================================================================

	private Connection
			mConnection;

	private String
			mDriverName,// = "oracle.jdbc.driver.OracleDriver",
			mConInfo,// = "jdbc:oracle:thin:mjanusze@//ikar.elka.pw.edu.pl:1521/elka.elka.pw.edu.pl",
			mLogin,// = "mjanusze",
			mPass;// = "mjanusze";

	private final int
			mRowsPerPage = 20;
}
